﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace AutoCodeCSharp.DBConnection
{

    public class DBTransaction
    {

        private SqlConnection mmsCon = 
            new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AutoCodeDBContext"].ToString());
        private SqlTransaction mmsTransaction = null;

        public void OpenConnection()
        {
            if (mmsCon.State == ConnectionState.Closed)
            {
                mmsCon.Open();

            }
        }


        public void CloseConnection()
        {
            if (mmsCon.State == ConnectionState.Open)
            {
                mmsCon.Close();

            }
        }


        public void BeginTransaction()
        {
            mmsTransaction = mmsCon.BeginTransaction(IsolationLevel.ReadUncommitted);

        }


        public void Commit()
        {
            mmsTransaction.Commit();

        }


        public void Rollback()
        {

            if (mmsCon.State == ConnectionState.Open)
            {
                mmsTransaction.Rollback();

            }

        }


        public void AddParameters(ref SqlCommand cmd, List<ParamClass> ListParam)
        {
            foreach (ParamClass p in ListParam)
            {
                SqlParameter sql_p = new SqlParameter(p.ParamName, p.ParamValue);
                cmd.Parameters.Add(sql_p);
            }

        }


        public int GetExecuteNum(string cmd)
        {

            int iRet = 0;

            SqlCommand mmsCmd = new SqlCommand(cmd, mmsCon);

            mmsCmd.Transaction = mmsTransaction;

            iRet = mmsCmd.ExecuteNonQuery();

            return iRet;

        }

        public int GetExecuteNum(string cmd, List<ParamClass> ListPara)
        {

            int iRet = 0;

            SqlCommand mmsCmd = new SqlCommand(cmd, mmsCon);

            mmsCmd.Parameters.Clear();

            mmsCmd.Transaction = mmsTransaction;

            AddParameters(ref mmsCmd, ListPara);

            iRet = mmsCmd.ExecuteNonQuery();

            return iRet;

        }

        public DataTable GetDataTable(string cmd, List<ParamClass> ListPara)
        {

            DataTable dtRet = new DataTable();

            SqlCommand mmsCmd = new SqlCommand(cmd, mmsCon);

            AddParameters(ref mmsCmd, ListPara);

            mmsCmd.Transaction = mmsTransaction;

            SqlDataAdapter adapter = new SqlDataAdapter(mmsCmd);

            adapter.Fill(dtRet);

            return dtRet;

        }

        public DataTable GetDataTable(string cmd)
        {

            DataTable dtRet = new DataTable();

            SqlCommand mmsCmd = new SqlCommand(cmd, mmsCon);

            mmsCmd.Transaction = mmsTransaction;

            SqlDataAdapter adapter = new SqlDataAdapter(mmsCmd);

            adapter.Fill(dtRet);

            return dtRet;

        }

        public int GetExecuteNum(string cmd, List<SqlParameter> ListPara)
        {

            int iRet = 0;

            SqlCommand mmsCmd = new SqlCommand(cmd, mmsCon);

            mmsCmd.Transaction = mmsTransaction;


            foreach (SqlParameter p in ListPara)
            {
                mmsCmd.Parameters.Add(p);
            }

            iRet = mmsCmd.ExecuteNonQuery();

            return iRet;

        }


        public static void ReplaceSQLParam(ref string Cmd, List<ParamClass> listParam)
        {

            foreach (ParamClass uParamClass in listParam)
            {
                Cmd = Cmd.Replace(uParamClass.ParamName, "'" + uParamClass.ParamValue.ToString().Replace("'", "") + "'");

            }

        }
    }



    public class ParamClass
    {
        public ParamClass(string _ParamName, object _ParamValue)
        {
            ParamName = _ParamName;
            ParamValue = _ParamValue;

        }

        public string ParamName { get; set; }

        public object ParamValue { get; set; }

    }
}